#-*- coding:utf-8 -*-
'''
Created on 2012-8-1

@author: johnny
'''
import copy
import db_connector


class DBQuery(object):
    def __init__(self, host, user='postgres', passwd='', db_name='fronware'):
        self.db = db_connector.DataBaseConnection()
        self.db_type = 'psql'
        self.host = host
        self.user = user
        self.passwd = passwd
        self.db_name = db_name
        self.retry_times = 10
    
    def query(self, table, keyword=None, column='*'):
        if keyword: 
            command = ('SELECT ' + column + ' from ' + table +
                       ' where ' + keyword)
        else:
            command = 'SELECT ' + column + ' from ' + table
            
        command = command + ';'
        
        return self.execute(command)
    
    def insert(self, table, keys, values):
        command = ("INSERT INTO " + str(table) + " " + 
                  keys + """ VALUES (%s, %s, %s, %s, %s,
                  %s, %s, %s, %s, %s, %s);""")
        if isinstance(values, tuple):
            return self.execute(command, values)
            
    def execute(self, command, parameters=None):
        self.db.connect(self.db_type, self.host, 5432,
                               self.user, self.passwd, self.db_name)
        result = self.db.execute(command, parameters)
        self.db.disconnect()
        return result
    
    def get_storageUuid(self, keyword=None):
        column = 'uuid'
        results = self.query('storage', keyword, column)
        results = [x[0] for x in results]
        if len(results) > 1:
            return results
        elif len(results) == 1:
            return results[0]
    
    def get_vm_storage(self, keyword=None):
        column = 'storage_path'
        results = self.query('vms', keyword, column)
        results = [x[0] for x in results]
        if len(results) > 1:
            return results
        elif len(results) == 1:
            return results[0]
    
    def get_vm_baseimg(self, vm_name):
        command = """SELECT baseimg from vms a, vms_img b
                  where a.id=b.vm_id and a.description=
                  '%s'""" % vm_name
        result = self.execute(command)
        if result:
            arr = [x[0] for x in result]
            if len(arr):
                return arr[0]
        else:
            return None
    
    def get_vm_vhd(self, vm_name):
        command = """SELECT b.* from vms a, vms_img b
                  where a.id=b.vm_id and a.description=
                  '%s'""" % vm_name
        result = self.execute(command)
        return result
    
    def get_storagepath(self, keyword=None):
        column = 'mount_path'
        results = self.query('storage', keyword, column)
        results = [x[0] for x in results]
        if len(results) > 1:
            return results
        elif len(results) == 1:
            return results[0]
    
    def get_hostUuid(self):
        column = 'uuid'
        results = self.query('hosts', '', column)
        results = [x[0] for x in results]
        if len(results) > 1:
            return results
        elif len(results) == 1:
            return results[0]
    
    def get_vmUuid(self, keyword=None):
        column = 'uuid'
        results = []
        times = 0       
        while not results and times < self.retry_times:
            results = self.query('vms', keyword, column)
            times += 1
        
        results = [x[0] for x in results]
        if len(results) > 1:
            return results
        elif len(results) == 1:
            return results[0]
    
    def get_vms_list(self):
        column = 'description'
        results = self.query('vms', column=column)
        results = [x[0] for x in results]
        if len(results) >= 1:
            return results
        
    def get_vnc_port(self, vm):
        column = 'vnc_port'
        keyword = "description=\'" + vm + "\'"
        results = self.query('vms', keyword, column)
        results = [x[0] for x in results]
        if len(results) > 1:
            return results
        elif len(results) == 1:
            return results[0]
        
    def get_vm_ip(self, vm):
        command = ("SELECT ip from vms a, netcard_vms b, " + 
                   " netcard_vms_ip c " + "where a.id=b.vm_id" + 
                   " and b.id=c.vnet_id and a.description=\'" + vm + "\';")
        
        result = self.execute(command)
        if result:
            arr = [x[0] for x in result]
            if len(arr):
                return arr[0]
        else:
            return 0
    
    def get_mission_stat(self, mid):
        column = 'task_execute_stat'
        keyword = "id=%s" % str(mid)
        results = self.query('missions', keyword, column)
        if results:
            arr = [x[0] for x in results]
            if len(arr):
                return arr[0]
        else:
            return 0
    
    def get_mission_message(self, mid):
        column = 'message'
        keyword = "id=%s" % str(mid)
        results = self.query('missions', keyword, column)
        if results:
            arr = [x[0] for x in results]
            if len(arr):
                return arr[0]
        else:
            return 0
        
    def get_mission_id_max(self, keyword=None):
        column = 'max(id)'
        results = self.query('missions', keyword, column)
        if results:
            arr = [x[0] for x in results]
            if len(arr):
                return arr[0]
        else:
            return 0
        
    def get_mission_id_min(self, keyword=None):
        column = 'min(id)'
        results = self.query('missions', keyword, column)
        if results:
            arr = [x[0] for x in results]
            if len(arr):
                return arr[0]
        else:
            return None
    
    def get_vmtype(self, vm):
        #TODO
        pass
        
    def get_snapshots(self, vmname):
        command = """SELECT a.uuid from snapshots a, vms b
         where b.id=a.vm_id and b.description='%s';""" % vmname
         
        result = self.execute(command)
        if result:
            arr = [x[0] for x in result]
            if len(arr):
                return arr
        else:
            return 0
        
    def is_current_snapshot(self, snapuuid):
        command = """SELECT parent_uuid from
        snapshots where description='You Are Here';""" 
         
        result = self.execute(command)
        
        if result:
            arr = [x[0] for x in result]
            if len(arr):
                for ll in arr:
                    if ll == snapuuid:
                        return True
            
                command = """SELECT description from
                snapshots where uuid='%s';""" % snapuuid
         
                result = self.execute(command)
                if result:
                    arr = [x[0] for x in result]
                    if len(arr):
                        arr = arr[0]
                    if arr == 'You Are Here' or arr == 'rootsnap':
                        return True
                    else:
                        return False
        
    